package connection;

import java.io.ByteArrayInputStream;
import java.security.cert.Certificate;
import java.security.cert.CertificateException;
import java.security.cert.CertificateFactory;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import security.keyManagement.KeyStoreManager;
import utils.Base64Processing;
import utils.PropertiesUtils;
import connection.exception.PropertiesUndefinedException;

public class DatabaseConnection {

	private Connection _dbConnection;

	private PreparedStatement _getUser;
	private PreparedStatement _getUserByUsername;
	private PreparedStatement _addUser;
	private PreparedStatement _getUserCompetency;
	private PreparedStatement _addUserCompetency;
	
	private PreparedStatement _getSerial;
	private PreparedStatement _setSerial;
	
	private PreparedStatement _addVericopSignature;
	private PreparedStatement _getVericopSignature;

	public DatabaseConnection(KeyStoreManager keyStoreManager, String propertiesAddress) throws PropertiesUndefinedException{
		Properties dbProperties = PropertiesUtils.getPropertiesFromFile(propertiesAddress);
		
		if(dbProperties == null)
			throw new PropertiesUndefinedException();
		
		String dbPassword = keyStoreManager.getDBServerPassword();

		Properties connectionProperties = new Properties();
		connectionProperties.put("user", dbProperties.getProperty("username"));
		connectionProperties.put("password", dbPassword);

		try{
			String targetDb = dbProperties.getProperty("dburl") + "/"+dbProperties.getProperty("dbname");

			_dbConnection = DriverManager.getConnection("jdbc:mysql://"+targetDb, connectionProperties);
			
		}catch (SQLException e) {
			e.printStackTrace();
		}

		configurePreparedStatements();
	}

	private void configurePreparedStatements(){
		String getUserQuery = "SELECT * FROM certcop.users WHERE FirstName = ? AND LastName = ?";
		String getUserByUsernameQuery = "SELECT * FROM certcop.users WHERE Username = ?";
		String addUserQuery = "INSERT INTO certcop.users (FirstName,LastName,Username,Password,Address,UserCert) VALUES ( ? , ? , ? , ? , ? , ? )";
		String getUserCompetencyQuery = "SELECT * FROM certcop.usercompetencies WHERE First_Name = ? AND Last_Name = ? AND Competency = ?";
		String addUserCompetencyQuery = "INSERT INTO certcop.usercompetencies (First_Name,Last_Name,Competency) VALUES ( ? , ? , ? )";
		
		String getSerialQuery = "SELECT * FROM certcop.serial";
        String setSerialQuery = "UPDATE certcop.serial SET serial = ?";
		
		String addVericopSignature = "INSERT INTO vericop.signatures (Serial,Signature) VALUES ( ? , ? )";
		String getVericopSignature = "SELECT * FROM vericop.signatures WHERE Serial = ?";

		try {
			_getUser = _dbConnection.prepareStatement(getUserQuery);
			_getUserByUsername = _dbConnection.prepareStatement(getUserByUsernameQuery);
			_addUser = _dbConnection.prepareStatement(addUserQuery);
			_getUserCompetency = _dbConnection.prepareStatement(getUserCompetencyQuery);
			_addUserCompetency = _dbConnection.prepareStatement(addUserCompetencyQuery);
			
			_getSerial = _dbConnection.prepareStatement(getSerialQuery);
			_setSerial = _dbConnection.prepareStatement(setSerialQuery);
			
			_addVericopSignature = _dbConnection.prepareStatement(addVericopSignature);
			_getVericopSignature = _dbConnection.prepareStatement(getVericopSignature);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public ResultSet getUser(String firstName, String lastName){
		try {
			_getUser.setString(1, firstName);
			_getUser.setString(2, lastName);
			
			ResultSet result = _getUser.executeQuery();
			result.last();
			
			if(result.getRow()==1){
				result.beforeFirst();
				_getUserCompetency.clearParameters();
				return result;
			}
			
			_getUser.clearParameters();
		} catch (SQLException e) {
			return null;
		}
		
		return null;
	}
	
	public String getUserPasswordHash(String user){
		try{
			_getUserByUsername.setString(1, user);
			
			ResultSet result = _getUserByUsername.executeQuery();
			result.last();
			
			if(result.getRow()==1){
				return result.getString("Password");
			}
		}catch (SQLException e) {
			return null;
		}
		
		return null;
	}
	
	public Certificate getUserCert(String fName, String lName) {
	    try{
            _getUser.setString(1, fName);
            _getUser.setString(2, lName);
            
            ResultSet result = _getUser.executeQuery();
            result.last();
            
            if(result.getRow()==1){
                ByteArrayInputStream bis = new ByteArrayInputStream(Base64Processing.decodeData(result.getString("UserCert")));
                CertificateFactory fact = CertificateFactory.getInstance("X509");
                return (Certificate) fact.generateCertificate(bis);
            }
        }catch (SQLException e) {
            e.printStackTrace();
        } catch (CertificateException e) {
        	return null;
        }
        
        return null;
	}

	public int addUser(String firstName, String lastName, String username, String password, String address, String userCert){
		try {
			_addUser.setString(1, firstName);
			_addUser.setString(2, lastName);
			_addUser.setString(3, username);
			_addUser.setString(4, password);
			_addUser.setString(5, address);
			_addUser.setString(6, userCert);
			
			int status = _addUser.executeUpdate();
			
			_addUser.clearParameters();
			
			return status;
		} catch (SQLException e) {
			return -1;
		}	
	}

	public ResultSet getUserCompetency(String firstName, String lastName, String competency){
		try {
			_getUserCompetency.setString(1, firstName);
			_getUserCompetency.setString(2, lastName);
			_getUserCompetency.setString(3, competency);
			
			ResultSet result = _getUserCompetency.executeQuery();
			result.last();
			
			if(result.getRow()==1){
				result.beforeFirst();
				_getUserCompetency.clearParameters();
				return result;
			}
			
			_getUserCompetency.clearParameters();
		} catch (SQLException e) {
			return null;
		}
		return null;
	}

	public int addUserCompetency(String firstName, String lastName, String competency){
		try {
			_addUserCompetency.setString(1, firstName);
			_addUserCompetency.setString(2, lastName);
			_addUserCompetency.setString(3, competency);
			
			
			int status = _addUserCompetency.executeUpdate();
			
			_addUserCompetency.clearParameters();
			
			return status;
		} catch (SQLException e) {
			return 0;
		}		
	}
	
	public Integer getSerial() {
	    try {
            
            ResultSet result = _getSerial.executeQuery();
            result.last();
            
            if(result.getRow()==1){
                _getSerial.clearParameters();
                return new Integer(result.getString("serial"));
            }
            
            _getVericopSignature.clearParameters();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return null;
	}
	
	public int setSerial(Integer value) {
	    try {
            _setSerial.setString(1, value.toString());
            
            int status = _setSerial.executeUpdate();
            
            _setSerial.clearParameters();
            
            return status;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return -1;
	}
	
	public int addVericopSignature(String serial, String sigValue){
        try {
            _addVericopSignature.setString(1, serial);
            _addVericopSignature.setString(2, sigValue);
            
            int status = _addVericopSignature.executeUpdate();
            
            _addVericopSignature.clearParameters();
            
            return status;
        } catch (SQLException e) {
        	return -1;
        }        
    }
	
	public String getVericopSignature(String serial){
        try {
            _getVericopSignature.setString(1, serial);
            
            ResultSet result = _getVericopSignature.executeQuery();
            result.last();
            
            if(result.getRow()==1){
                _getVericopSignature.clearParameters();
                return result.getString("Signature");
            }
            
            _getVericopSignature.clearParameters();
        } catch (SQLException e) {
        	return null;
        }
        
        return null;
    }
}
